package dao;

import pojo.ClassicFormula;
import pojo.Tcm;
import util.DbUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class ClassicFormulaDao {
    private Connection conn = null;
    private PreparedStatement pstm = null;
    private ResultSet rs = null;

    //展示
    public List<ClassicFormula> getClassicFormulaList(int currentPage,int pageSize){
        List<ClassicFormula> classicFormulaList = new ArrayList<>();
        try{
            conn  = DbUtil.getConnection();
            String sql = "select * from classicformula limit "+pageSize*(currentPage-1)+","+pageSize;
            pstm = conn.prepareStatement(sql);
            rs = pstm.executeQuery(sql);
            while(rs.next()){
                ClassicFormula cf = new ClassicFormula();
                cf.setId(rs.getInt("id"));
                cf.setName(rs.getString("name"));
                cf.setAlias(rs.getString("alias"));
                cf.setUsage(rs.getString("usage"));
                cf.setComment(rs.getString("comment"));
                classicFormulaList.add(cf);
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            DbUtil.close(conn, pstm, rs);
        }
        return classicFormulaList;
    }

//    获取总条数
    public int getTotalNum(){
        int totalNum=0;
        try{
            conn  = DbUtil.getConnection();
            String sql = "select count(*) as totalnum from classicformula";
            pstm = conn.prepareStatement(sql);
            rs = pstm.executeQuery(sql);
            while(rs.next()){
                totalNum=rs.getInt("totalnum");
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            DbUtil.close(conn, pstm, rs);
        }
        return totalNum;
    }


    //    关键字查询 名称与别名
    public List<ClassicFormula> getQueryCFList(String key,int flag){
        List<ClassicFormula> cFList = new ArrayList<>();
        String sql=null;
//        0名称查询
//        1别名查询
//        2中药名查询
        switch (flag){
            case 0:{
                sql="select * from classicformula where name like '%"+key+"%'";
                break;
            }
            case 1:{
                sql="select * from classicformula where alias like '%"+key+"%'";
                break;
            }
            case 2:{
                sql="select classicformula.id,classicformula.name,classicformula.alias,classicformula.usage,classicformula.comment " +
                        "from classicformula,classicformulacomposition,tcm " +
                        "where classicformula.id=classicformulacomposition.cfid and tcm.id=classicformulacomposition.tcmid and tcm.name like '%"+key+"%'";
                break;
            }
            default:{
                System.out.println("未知错误");
                break;
            }
        }
        try{
            conn  = DbUtil.getConnection();
            pstm = conn.prepareStatement(sql);
            rs = pstm.executeQuery(sql);
            while(rs.next()){
                ClassicFormula classicFormula=new ClassicFormula();
                classicFormula.setId(rs.getInt("id"));
                classicFormula.setName(rs.getString("name"));
                classicFormula.setAlias(rs.getString("alias"));
                classicFormula.setUsage(rs.getString("usage"));
                classicFormula.setComment(rs.getString("comment"));
                cFList.add(classicFormula);
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            DbUtil.close(conn, pstm, rs);
        }
        return cFList;
    }

//  获取详细信息
    public ClassicFormula getClassicFormulaById(int id){
        ClassicFormula cF = new ClassicFormula();
        try{
            conn  = DbUtil.getConnection();
            String sql = "select * from classicFormula where id =?";
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1,id);
            rs = pstm.executeQuery();
            while(rs.next()){
                cF.setId(rs.getInt("id"));
                cF.setUsage(rs.getString("usage"));
                cF.setName(rs.getString("name"));
                cF.setAlias(rs.getString("alias"));
                cF.setComment(rs.getString("comment"));
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            DbUtil.close(conn, pstm, rs);
        }
        return cF;
    }
}
